﻿using System;
using System.Collections.Generic;
using System.Web;
using MoiGioiHDBank.DTO;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace MoiGioiHDBank.DAO
{
    public class SIDDAO
    {
        public static string LaySID(string SIDString)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT * FROM SID WHERE (SID = @SID)";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "SID";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;
            para.Value = SIDString;
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            else
            {
                SIDDTO SID = new SIDDTO();
                SID.SID = dt.Rows[0]["SID"].ToString();
                return SID.SID;
            }
        }

        public static SIDDTO LayThongTinSID(string sidstring)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT * FROM SID WHERE (SID = @sidstring)";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "sidstring";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;
            para.Value = sidstring;
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                SIDDTO SID = new SIDDTO();
                SID.UserName = dt.Rows[0]["USERNAME"].ToString();
                SID.SID = dt.Rows[0]["SID"].ToString();
                SID.MaNhomKhachHang = (int)dt.Rows[0]["MANHOMKHACHHANG"];
                SID.SoThe =MineUtils.GiaiMa(MineUtils.StringToByteArray(dt.Rows[0]["SOTHE"].ToString()));
                return SID;
            }
        }

        public static int ThemSID(string SIDString, string username, int manhomkhachhang, string sothe)
        {

            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"insert SID(SID,USERNAME,MANHOMKHACHHANG,SOTHE) values('" + SIDString + "','" + username + "'," + manhomkhachhang.ToString() + ",'" + MineUtils.ByteArrayToString(MineUtils.MaHoa(sothe)) + "')";

            SqlCommand sql = new SqlCommand(sqlString);
            int i = dl.ThucThiKhongTruyVan(sql);
            return i;
        }

        public static int XoaSID(string SIDString)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"delete from SID where SID='" + SIDString + "'";

            SqlCommand sql = new SqlCommand(sqlString);
            int i = dl.ThucThiKhongTruyVan(sql);
            return i;
        }

        public static string LaySIDUsername(string username)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT * FROM SID WHERE (USERNAME = @username)";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "username";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;
            para.Value = username;
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            else
            {
                SIDDTO SID = new SIDDTO();
                SID.SID = dt.Rows[0]["SID"].ToString();
                return SID.SID;
            }

        }
        public static SIDDTO LaySIDDTO(string username)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT * FROM SID WHERE (USERNAME = @username)";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "username";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;
            para.Value = username;
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                SIDDTO SID = new SIDDTO();
                SID.UserName = dt.Rows[0]["USERNAME"].ToString();
                SID.SID = dt.Rows[0]["SID"].ToString();
                SID.MaNhomKhachHang = (int)dt.Rows[0]["MANHOMKHACHHANG"];
                return SID;
            }

        }


    }
}